﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DelonixRegiaSystem.Classes
{
    class housekeepingJhDBManager
    {
        public static int InsertHousekeeping(housekeeping r)
        {
            int rowsinserted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO housekeeping(roomNum,staffName,date)"
                    + " VALUES (@roomNum,@staffName,@date)";
                comm.Parameters.AddWithValue("@roomNum", r.roomNum);
                comm.Parameters.AddWithValue("@staffName", r.staffName);
                comm.Parameters.AddWithValue("@date", r.date);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }
        public static int UpdateHousekeeping(housekeeping c)
        {
            int rowsupdated = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE housekeeping SET roomNum=@roomNum, staffName=@staffName, date=@date where housekeepingNum=@housekeepingNum";
                comm.Parameters.AddWithValue("@roomNum", c.roomNum);
                comm.Parameters.AddWithValue("@staffName", c.staffName);
                comm.Parameters.AddWithValue("@date", c.date);
                comm.Parameters.AddWithValue("@housekeepingNum", c.housekeepingNum);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }
        public static ArrayList GetStaffByDutyType(string dutyType)
        {
            ArrayList Staff = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from staff WHERE dutyType=@dutyType";
                comm.Parameters.AddWithValue("@dutyType", dutyType);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    staff r = new staff();
                    r.staffName = (string)dr["staffName"];
                    r.staffType = (string)dr["staffType"];
                    r.dutyType = (string)dr["dutyType"];
                    Staff.Add(r);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return Staff;
        }
        public static ArrayList GetDutyByStaffName(string staffName)
        {
            ArrayList housekeeping = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from housekeeping WHERE staffName=@staffName";
                comm.Parameters.AddWithValue("@staffName", staffName);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    housekeeping r = new housekeeping();
                    r.staffName = (string)dr["staffName"];
                    r.housekeepingNum = (int)dr["housekeepingNum"];
                    r.date = (string)dr["date"];
                    r.roomNum = (int)dr["roomNum"];
                    housekeeping.Add(r);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return housekeeping;
        }
        public static ArrayList GetHouseNumByStaff(string staffName)
        {
            ArrayList HouseNum = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from housekeeping WHERE staffName=@staffName";
                comm.Parameters.AddWithValue("@staffName", staffName);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    housekeeping r = new housekeeping();
                    r.staffName = (string)dr["staffName"];
                    r.date = (string)dr["date"];
                    r.roomNum = (int)dr["roomNum"];
                    r.housekeepingNum = (int)dr["housekeepingNum"];
                    HouseNum.Add(r);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return HouseNum;
        }
        public static ArrayList GetHouseByDate(string date)
        {
            ArrayList HouseNum = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from housekeeping WHERE date=@date";
                comm.Parameters.AddWithValue("@date", date);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    housekeeping r = new housekeeping();
                    r.staffName = (string)dr["staffName"];
                    r.date = (string)dr["date"];
                    r.roomNum = (int)dr["roomNum"];
                    r.housekeepingNum = (int)dr["housekeepingNum"];
                    HouseNum.Add(r);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return HouseNum;
        }
    }
}
